package com.demoweb.model.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.demoweb.model.dto.Member;

public class MemberDao {

	public void insertMember(Member member) {

		Connection conn = null;
		PreparedStatement pstmt = null;

		try {
//			// 1. 드라이버 로딩
//			Class.forName("com.mysql.jdbc.Driver");
//			// 2. 연결 생성
//			conn = DriverManager.getConnection(
//					"jdbc:mysql://localhost:3306/newdemoweb", "forbm123",
//					"mysql");
			
			conn = ConnectionHelper.getConnection();	// 커넥션 pool 을 이용하여 연결
			
			
			// 3. 명령 생성
			String sql = "INSERT INTO member "
					+ "(memberid, passwd, email, usertype, active)"
					+ "VALUES ( ?, ?, ?, ?, ?);";

			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, member.getMemberId());
			pstmt.setString(2, member.getPasswd());
			pstmt.setString(3, member.getEmail());
			pstmt.setString(4, member.getUserType());
			pstmt.setBoolean(5, member.isActive());

			// 4. 명령 실행
			pstmt.executeUpdate();

		} catch (Exception e) {

		} finally {
			try {
				pstmt.close();
			} catch (Exception ex) {
			}
			try {
				conn.close();
			} catch (Exception ex) {
			}
		}
	}

	public List<Member> getMember() { // 반환값의 List를 사용하는 이유 : 반환값은 일반화 된 타입을
										// 사용하는게 좋다. 클래스 계층 구조의 상위층
										// LinkedList, ArrayList 등의 List 사용이
										// 유기적으로 이루어진다.
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null; // Select 조회
		ArrayList<Member> members = new ArrayList<Member>();

		try {
//			// 1. 드라이버 로딩
//			Class.forName("com.mysql.jdbc.Driver");
//			// 2. 연결 생성
//			conn = DriverManager.getConnection(
//					"jdbc:mysql://localhost:3306/newdemoweb", "forbm123",
//					"mysql");
			
			conn = ConnectionHelper.getConnection();	// 커넥션 pool 을 이용하여 연결
			
			
			// 3. 명령 생성
			String sql = "SELECT memberid, email, usertype, active, regdate "
					+ "FROM member";
			pstmt = conn.prepareStatement(sql);
			// 4. 명령 실행
			rs = pstmt.executeQuery();
			// 5. 조회 결과를 처리 (SELECT QUERY인 경우)
			while (rs.next()) {
				Member member = new Member();
				member.setMemberId(rs.getString(1));
				member.setEmail(rs.getString(2));
				member.setUserType(rs.getString(3));
				member.setActive(rs.getBoolean(4));
				member.setRegDate(rs.getDate(5));

				members.add(member);
			}
		} catch (Exception e) {

		} finally {
			try {
				rs.close();
			} catch (Exception ex) {
			}
			try {
				pstmt.close();
			} catch (Exception ex) {
			}
			try {
				conn.close();
			} catch (Exception ex) {
			}
		}

		return members;
	}

	//아이디 "일치 조회" 메서드 (단일 객체 반환)
	public Member getMemberById(String id) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Member member = null;

		try {
//			// 1. 드라이버 로딩
//			Class.forName("com.mysql.jdbc.Driver");
//			// 2. 연결 생성
//			conn = DriverManager.getConnection(
//					"jdbc:mysql://localhost:3306/newdemoweb", "forbm123",
//					"mysql");
			
			conn = ConnectionHelper.getConnection();	// 커넥션 pool 을 이용하여 연결

			pstmt = conn.prepareStatement("SELECT memberid, email, usertype, active, regdate "
							+ "FROM member WHERE memberid = ?");
			pstmt.setString(1, id);

			rs = pstmt.executeQuery();// executeQuery : select 실행

			if (rs.next()) {// 단일 건 검색이므로 while 대신 if 사용
				member = new Member();
				member.setMemberId(rs.getString(1));
				member.setEmail(rs.getString(2));
				member.setUserType(rs.getString(3));
				member.setActive(rs.getBoolean(4));
				member.setRegDate(rs.getDate(5));
			}
		} catch (Exception ex) {
			ex.printStackTrace();// 오류 메시지를 Console환경에 출력
		} finally {
			try { rs.close(); } catch (Exception ex) {}
			try { pstmt.close();} catch (Exception ex) {}
			try { conn.close(); } catch (Exception ex) { }
		}

		return member;
	}
	

	public Member getMemberByIdAndPasswd(String id, String passwd) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Member member = null;

		try {
			conn = ConnectionHelper.getConnection();	// 커넥션 pool 을 이용하여 연결

			pstmt = conn.prepareStatement("SELECT memberid, email, usertype, active, regdate "
							+ "FROM member WHERE memberid = ? AND passwd = ?");

			pstmt.setString(1, id);
			pstmt.setString(2, passwd);

			rs = pstmt.executeQuery();// executeQuery : select 실행

			if (rs.next()) {// 단일 건 검색이므로 while 대신 if 사용
				member = new Member();
				member.setMemberId(rs.getString(1));
				member.setEmail(rs.getString(2));
				member.setUserType(rs.getString(3));
				member.setActive(rs.getBoolean(4));
				member.setRegDate(rs.getDate(5));
			}
		} catch (Exception ex) {
			ex.printStackTrace();// 오류 메시지를 Console환경에 출력
		} finally {
			try { rs.close(); } catch (Exception ex) {}
			try { pstmt.close();} catch (Exception ex) {}
			try { conn.close(); } catch (Exception ex) { }
		}

		return member;
	}
}
